CREATE OR REPLACE FUNCTION gsis_user_data (p_afm IN varchar2, p_field IN varchar2)
--Working
  RETURN varchar2
AS
  l_service          UTL_DBWS.service;
  l_call             UTL_DBWS.call;
  
  l_wsdl_url         VARCHAR2(32767);
  l_namespace        VARCHAR2(32767);
  l_service_qname    UTL_DBWS.qname;
  l_port_qname       UTL_DBWS.qname;
  l_operation_qname  UTL_DBWS.qname;

  l_xmltype_in       SYS.XMLTYPE;
  l_xmltype_out      SYS.XMLTYPE;
  l_return           VARCHAR2(32767);
BEGIN
  --l_wsdl_url        := 'http://www.oracle-base.com/webservices/server.php?wsdl';
  --l_wsdl_url        := 'http://www1.gsis.gr/wsgsis/RgWsBasStoixN/RgWsBasStoixNSoapHttpPort?WSDL';
  l_wsdl_url        := 'http://www.gsis.gr/wsnp/RgWsBasStoixN_version2.wsdl';  
  
  --l_namespace       := 'http://www.oracle-base.com/webservices/';
  l_namespace       := 'http://gr/gsis/rgwsbasstoixn/RgWsBasStoixN.wsdl'; 

  --l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'Calculator');
  l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'RgWsBasStoixN');  
  --l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'CalculatorPort');  
  l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'RgWsBasStoixNSoapHttpPort');
  --l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'ws_add');
  l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'rgWsBasStoixEpit'); --rgWsBasStoixN
  

  l_service := UTL_DBWS.create_service (
    wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
    service_name           => l_service_qname);

  l_call := UTL_DBWS.create_call (
    service_handle => l_service,
    port_name      => l_port_qname,
    operation_name => l_operation_qname);


    --in order to find the correct xml i had to check the apex version that generates soapenv and adjust accordingly 
  l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
    <ns1:rgWsBasStoixEpit xmlns:ns1="' || l_namespace || '" 
                      xmlns:typ= "http://gr/gsis/rgwsbasstoixn/RgWsBasStoixN.wsdl/types/" 
    >
      <pAfm>' || p_afm || '</pAfm>     
      <pBasStoixEpitRec_out> 
            <typ:residenceParDescription></typ:residenceParDescription> 
            <typ:assTxpActualAfm></typ:assTxpActualAfm> 
            <typ:actLongDescr></typ:actLongDescr> 
            <typ:postalZipCode></typ:postalZipCode> 
            <typ:INiFlagDescr></typ:INiFlagDescr> 
            <typ:registDate>2011-01-01</typ:registDate> 
            <typ:stopDate>2011-01-01</typ:stopDate> 
            <typ:parDescription></typ:parDescription>
            <typ:doyDescr></typ:doyDescr> 
            <typ:residenceZipCode></typ:residenceZipCode> 
            <typ:deactivationFlagDescr></typ:deactivationFlagDescr> 
            <typ:legalStatusDescr></typ:legalStatusDescr> 
            <typ:firmPhone></typ:firmPhone> 
            <typ:firmFax></typ:firmFax> 
            <typ:afm></typ:afm> 
            <typ:facActivity>0</typ:facActivity> 
            <typ:countOfBranches>0</typ:countOfBranches> 
            <typ:deactivationFlag>1</typ:deactivationFlag>
            <typ:postalAddressNo></typ:postalAddressNo> 
            <typ:firmFlagDescr></typ:firmFlagDescr> 
            <typ:postalAddress></typ:postalAddress> 
            <typ:doy></typ:doy> 
            <typ:onomasia></typ:onomasia> 
            <typ:commerTitle></typ:commerTitle> 
         </pBasStoixEpitRec_out> 
       <pCallSeqId_out>0</pCallSeqId_out> 
       <pErrorRec_out> 
            <typ:errorDescr></typ:errorDescr> 
            <typ:errorCode></typ:errorCode> 
       </pErrorRec_out> 
    </ns1:rgWsBasStoixEpit>');

  l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
                                   request     => l_xmltype_in);
  
  UTL_DBWS.release_call (call_handle => l_call);
  UTL_DBWS.release_service (service_handle => l_service);

  --l_return := l_xmltype_out.getstringval;
  --l_return := l_xmltype_out.extract('/*/onomasia/text()').getStringVal();
   select 
     extractValue(value(t),'/*/' || p_field,'xmlns="http://gr/gsis/rgwsbasstoixn/RgWsBasStoixN.wsdl/types/"') 
    into l_return
   from table(xmlsequence(extract(l_xmltype_out,'//pBasStoixEpitRec_out'))) t; 

    --xistsNode() and extract() 

  
  RETURN l_return;
END;
/